<?php
class OLM_Cron
{
	static function updateProductStatus()
	{
		$sales_type = Q::ini('custom_flag/inventory_log_type/sales/value');
		$end = CURRENT_DATETIME;
		$week_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -7 days'));
		$month_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -30 days'));
		$three_months_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -90 days'));
		$rising = Q::ini('custom_flag/product_sales_trend/rising/value');
		$decline = Q::ini('custom_flag/product_sales_trend/decline/value');
		$product = Product_Warehouse::find()->setColumns('id, product_basic_id, warehouse_id, lock_flag, sales_status, actual_quantity, available_quantity, sold_quantity, purchase_status, lock_safe_quantity_days, lock_warning_quantity_days')->asArray()->getAll();
		
		//产品销售状态
		$sales_new = Q::ini('custom_flag/product_sales_status/new_product/value');
		$normal = Q::ini('custom_flag/product_sales_status/normal_sales/value');
		$sold_out = Q::ini('custom_flag/product_sales_status/sold_out/value');
		$out_of_stock = Q::ini('custom_flag/product_sales_status/out_of_stock/value');
		$temporarily_off = Q::ini('custom_flag/product_sales_status/temporarily_off/value');
		
		//产品采购状态
		$purchase_new = Q::ini('custom_flag/product_purchase_status/new/value');
		$sufficient = Q::ini('custom_flag/product_purchase_status/sufficient/value');
		$restock = Q::ini('custom_flag/product_purchase_status/restock/value');
		
		foreach ($product as $p)
		{
			//周销、月销、三个月平均月销
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>=? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $week_ago, $end)->sum('out_quantity')->asArray()->getOne();
			$weekly_sales = intval($log['sum_value']);
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>=? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $month_ago, $end)->sum('out_quantity')->asArray()->getOne();
			$monthly_sales = intval($log['sum_value']);
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>=? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $three_months_ago, $end)->sum('out_quantity')->asArray()->getOne();
			$three_months_avg_sales = intval($log['sum_value'] / 3);
			
			//销售趋势
			$sales_trend = (($weekly_sales) * 4 >= ($monthly_sales * 0.7 + $three_months_avg_sales *0.3)) ? $rising : $decline;
			
			//判断是否按锁定天数更新
			if ($p['lock_flag'])
			{
				$safe_days = $p['lock_safe_quantity_days'] ? $p['lock_safe_quantity_days'] : 20;
				if ($p['lock_warning_quantity_days'])
				{
					$warning_days_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . '-' . $p['lock_warning_quantity_days'] . ' day'));
				}
				else
				{
					$warning_days_ago = $week_ago;
				}
			}
			else
			{
				$safe_days = 20;
				$warning_days_ago = $week_ago;
			}
			
			//安全库存
			$safe_quantity = ($sales_trend == $rising) ? (int)(($monthly_sales / 30 * $safe_days + $p['sold_quantity']) * 1.2) : (int)(($monthly_sales / 30 * $safe_days + $p['sold_quantity']) * 0.8);
			
			//警告库存
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>=? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $warning_days_ago, $end)->sum('out_quantity')->asArray()->getOne();
			$warning_days_sales = intval($log['sum_value']);
			$warning_quantity = (int)($warning_days_sales + $p['sold_quantity']);
			
			$update_sql = 'weekly_sales=' . $weekly_sales . ',monthly_sales=' . $monthly_sales . ',sales_trend=' . $sales_trend . ',three_months_avg_sales=' . $three_months_avg_sales . ',safe_quantity=' . $safe_quantity . ',warning_quantity=' . $warning_quantity;

			//产品销售状态更新sql组装
			if ($p['sales_status'] == $sales_new && $p['sold_quantity'] > 0)
			{
				$update_sql .= ',sales_status=' . $normal . ',last_change_sales_status_time=\'' . CURRENT_DATETIME . '\',last_change_sales_status_user_id=0';
			}
			if ($p['sales_status'] == $sold_out && $p['actual_quantity'] <= 0)
			{
				$update_sql .= ',sales_status=' . $out_of_stock . ',last_change_sales_status_time=\'' . CURRENT_DATETIME .'\',last_change_sales_status_user_id=0';
			}
			
			//产品采购状态更新sql组装
			if ($p['purchase_status'] == $purchase_new && $p['actual_quantity'] > 0 && $p['sold_quantity'] <= $p['actual_quantity'])
			{
				$update_sql .= ',purchase_status=' . $sufficient . ',last_change_purchase_status_time=\'' . CURRENT_DATETIME . '\',last_change_purchase_status_user_id=0';
			}
			if ($p['purchase_status'] == $purchase_new && ($p['sales_status'] == $sales_new || $p['sales_status'] == $normal) && $p['actual_quantity'] < $p['sold_quantity'])
			{
				$update_sql .= ',purchase_status=' . $restock . ',last_change_purchase_status_time=\'' . CURRENT_DATETIME . '\',last_change_purchase_status_user_id=0';
			}
			if ($p['purchase_status'] == $sufficient && ($p['sales_status'] == $sales_new || $p['sales_status'] == $normal || $p['sales_status'] == $temporarily_off) && $p['actual_quantity'] < $warning_quantity)
			{
				$update_sql .= ',purchase_status=' . $restock . ',last_change_purchase_status_time=\'' . CURRENT_DATETIME . '\',last_change_purchase_status_user_id=0';
			}
			if ($p['purchase_status'] == $restock && ($p['sales_status'] == $sales_new || $p['sales_status'] == $normal || $p['sales_status'] == $temporarily_off) && $p['actual_quantity'] >= $warning_quantity)
			{
				$update_sql .= ',purchase_status=' . $sufficient . ',last_change_purchase_status_time=\'' . CURRENT_DATETIME . '\',last_change_purchase_status_user_id=0';
			}
			QDB::getConn()->execute('update product_warehouse set ' . $update_sql . ' where id=' . $p['id']);
		}
		return array('ack' => SUCCESS, 'message' => 'success updateProductStatus, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function updateProductWarningStatus()
	{
		$sales_type = Q::ini('custom_flag/inventory_log_type/sales/value');
		$end = CURRENT_DATETIME;
		$first_week_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -7 days'));
		$second_week_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -14 days'));
		$third_week_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -21 days'));
		$fourth_week_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -28 days'));
		$ninety_day_ago = date('Y-m-d H:i:s', strtotime(CURRENT_DATETIME . ' -90 days'));
		$product = Product_Warehouse::find()->setColumns('id, product_basic_id, warehouse_id, weekly_sales, warning_status')->asArray()->getAll();
		foreach ($product as $p)
		{
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $first_week_ago, $end)->sum('out_quantity')->asArray()->getOne();
			$first_week_out = intval($log['sum_value']);
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $second_week_ago, $first_week_ago)->sum('out_quantity')->asArray()->getOne();
			$second_week_out = intval($log['sum_value']);
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $third_week_ago, $second_week_ago)->sum('out_quantity')->asArray()->getOne();
			$third_week_out = intval($log['sum_value']);
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $fourth_week_ago, $third_week_ago)->sum('out_quantity')->asArray()->getOne();
			$fourth_week_out = intval($log['sum_value']);
			$log = Inventory_Log::find('warehouse_id=? and product_basic_id=? and type=? and time>? and time<=?', $p['warehouse_id'], $p['product_basic_id'], $sales_type, $ninety_day_ago, $end)->sum('out_quantity')->asArray()->getOne();
			$ninety_day_out = intval($log['sum_value']);
			//计分1：最近3周销售出库量
			$three_week_amount_out = $first_week_out + $second_week_out + $third_week_out;
			$scoring = $three_week_amount_out == 0 ? 3 : (($three_week_amount_out > 0 && $three_week_amount_out <= 3) ? 2 : 0);
			//计分2：最近两周增长率比较
			$first_week_growth_rate = $second_week_out == 0 ? $first_week_out : ($first_week_out - $second_week_out) / $second_week_out;
			$second_week_growth_rate = $third_week_out == 0 ? $second_week_out : ($second_week_out - $third_week_out) / $third_week_out;
			$scoring += ($first_week_growth_rate - $second_week_growth_rate >= 0) ? 0 : 1;
			//计分3：最近3周增长率平均数情况
			$third_week_growth_rate = $fourth_week_out == 0 ? $third_week_out : ($third_week_out - $fourth_week_out) / $fourth_week_out;
			$result = ($first_week_growth_rate + $second_week_growth_rate + $third_week_growth_rate) / 3;
			$scoring += $result <= 0 ? 3 : (($result > 0 && $result < 0.083) ? 2 : (($result >= 0.083 && $result < 0.16) ? 1 : 0));
			//计分4：最近3月销售情况对比
			$result = $p['weekly_sales'] * 12 - $ninety_day_out;
			$scoring += $result < 0 ? 0.8 : ($result == 0 ? 0.4 : 0);
			//计分5：上次更新的警告状态
			$scoring += $p['warning_status'] == Q::ini('custom_flag/product_warning_status/warning/value') ? 0.8 : ($p['warning_status'] == Q::ini('custom_flag/product_warning_status/preliminary_warning/value') ? 0.4 : 0);			
			$warning_status = $scoring <= 3 ? Q::ini('custom_flag/product_warning_status/normal/value') : (($scoring > 3 && $scoring <= 6) ? Q::ini('custom_flag/product_warning_status/preliminary_warning/value') : Q::ini('custom_flag/product_warning_status/warning/value'));
			QDB::getConn()->execute('update product_warehouse set warning_status=' . $warning_status . ' where id=' . $p['id']);
		}
		return array('ack' => SUCCESS, 'message' => 'success updateProductWarningStatus, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function fixProductQuantity()
	{
		$default_warehouse_id = Warehouse::getDefaultWarehouseId();
		$sales_lock_where = 'sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value') . ' and sales_order.handle_status in (' . Q::ini('custom_flag/sales_order_handle_status/assigned/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/printed/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/printed_address/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/weighed/value') . ')';
		$other_out_lock_where = 'other_io.type=' . Q::ini('custom_flag/other_io_type/out/value') . ' and other_io.storage_audit=' . Q::ini('custom_flag/other_io_storage_audit/unaudited/value');
		$purchase_drawback_lock_where = 'purchase_order.type=' . Q::ini('custom_flag/purchase_order_type/drawback/value') . ' and purchase_order.status!=' . Q::ini('custom_flag/purchase_order_status/storaged/value');
		$fab_allocate_lock_where = 'fba_allocate.status=' . Q::ini('custom_flag/fba_allocate_status/new/value');
		$sold_where = 'sales_order.trash_status=' . Q::ini('custom_flag/sales_order_trash_status/normal/value') . ' and sales_order.handle_status in (' . Q::ini('custom_flag/sales_order_handle_status/new/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/checkout/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/printed/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/printed_address/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/weighed/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/sales_hold/value') . ',' . Q::ini('custom_flag/sales_order_handle_status/warehouse_hold/value') . ')';
		$purchase_budget_where = 'purchase_order.type=' . Q::ini('custom_flag/purchase_order_type/order/value') . ' and purchase_order.status!=' . Q::ini('custom_flag/purchase_order_status/storaged/value') . ' and purchase_order_item.audit_flag=' . Q::ini('custom_flag/purchase_order_item_audit_flag/unaudited/value');
		$purchase_actual_where = 'purchase_order.type=' . Q::ini('custom_flag/purchase_order_type/order/value') . ' and (purchase_order.status=' . Q::ini('custom_flag/purchase_order_status/arrived/value') . ' or purchase_order.status=' . Q::ini('custom_flag/purchase_order_status/partial_storaged/value') . ') and purchase_order_item.audit_flag=' . Q::ini('custom_flag/purchase_order_item_audit_flag/unaudited/value');
		$purchase_quantity_where = 'purchase_order.type=' . Q::ini('custom_flag/purchase_order_type/order/value') . ' and (purchase_order.status=' . Q::ini('custom_flag/purchase_order_status/purchased/value'). ' or purchase_order.status=' . Q::ini('custom_flag/purchase_order_status/arrived/value') . ' or purchase_order.status=' . Q::ini('custom_flag/purchase_order_status/partial_storaged/value') . ') and purchase_order_item.audit_flag=' . Q::ini('custom_flag/purchase_order_item_audit_flag/unaudited/value');
		
		$product = Product_Warehouse::find('warehouse_id=?', $default_warehouse_id)->setColumns('id, storage_number, product_basic_id, warehouse_id, actual_quantity, available_quantity, purchase_budget_quantity, purchase_actual_quantity, sold_quantity, purchase_quantity')->asArray()->getAll();
		foreach ($product as $p)
		{
			$sales = QDB::getConn()->execute('select sum(sales_order_item.quantity) as locking from sales_order_item left join sales_order on sales_order.id=sales_order_item.sales_order_id where ' . $sales_lock_where . ' and sales_order.warehouse_id=' . $p['warehouse_id'] . ' and sales_order_item.product_basic_id=' . $p['product_basic_id'])->fetchRow();
			$other_out = QDB::getConn()->execute('select sum(other_io_item.quantity) as locking from other_io_item left join other_io on other_io.id=other_io_item.other_io_id where ' . $other_out_lock_where . ' and other_io.warehouse_id=' . $p['warehouse_id'] . ' and other_io_item.product_basic_id=' . $p['product_basic_id'])->fetchRow();
			$fba_allocate = QDB::getConn()->execute('select sum(fba_allocate_item.quantity) as locking from fba_allocate_item left join fba_allocate on fba_allocate.id=fba_allocate_item.fba_allocate_id where ' . $fab_allocate_lock_where . ' and fba_allocate_item.product_basic_id=' . $p['product_basic_id'])->fetchRow();
			$purchase_drawback = QDB::getConn()->execute('select sum(purchase_order_item.actual_quantity) as locking from purchase_order_item left join purchase_order on purchase_order.id=purchase_order_item.purchase_order_id where ' . $purchase_drawback_lock_where . ' and purchase_order.warehouse_id=' . $p['warehouse_id'] . ' and purchase_order_item.product_basic_id=' . $p['product_basic_id'])->fetchRow();
			$sold = QDB::getConn()->execute('select sum(sales_order_item.quantity) as sold_quantity from sales_order_item left join sales_order on sales_order.id=sales_order_item.sales_order_id where ' . $sold_where . ' and sales_order.warehouse_id=' . $p['warehouse_id'] . ' and sales_order_item.product_basic_id=' . $p['product_basic_id'])->fetchRow();
			$purchase_budget = QDB::getConn()->execute('select sum(purchase_order_item.budget_quantity) as purchase_budget_quantity from purchase_order_item left join purchase_order on purchase_order.id=purchase_order_item.purchase_order_id where ' . $purchase_budget_where . ' and purchase_order.warehouse_id=' . $p['warehouse_id'] . ' and purchase_order_item.product_basic_id=' .$p['product_basic_id'])->fetchRow();
			$purchase_actual = QDB::getConn()->execute('select sum(purchase_order_item.actual_quantity) as purchase_actual_quantity from purchase_order_item left join purchase_order on purchase_order.id=purchase_order_item.purchase_order_id where ' . $purchase_actual_where . ' and purchase_order.warehouse_id=' . $p['warehouse_id'] . ' and purchase_order_item.product_basic_id=' .$p['product_basic_id'])->fetchRow();
			$purchase_quantity = QDB::getConn()->execute('select sum(purchase_order_item.actual_quantity) as purchase_quantity from purchase_order_item left join purchase_order on purchase_order.id=purchase_order_item.purchase_order_id where ' . $purchase_quantity_where . ' and purchase_order.warehouse_id=' . $p['warehouse_id'] . ' and purchase_order_item.product_basic_id=' .$p['product_basic_id'])->fetchRow();
			$available_quantity = $p['actual_quantity'] - intval($sales['locking']) - intval($other_out['locking']) - intval($purchase_drawback['locking']) - intval($fba_allocate['locking']);
			$set = array();
			$message = '';
			$fix_available_quantity = $fix_purchase_quantity = $fix_purchase_actual_quantity = $fix_purchase_budget_quantity = $fix_sold_quantity = 0;
			if ($available_quantity != $p['available_quantity'])
			{
				$set[] = 'available_quantity=' . $available_quantity;
				$fix_available_quantity = (int)($available_quantity - $p['available_quantity']);
			}
			if ((int)$purchase_quantity['purchase_quantity'] != $p['purchase_quantity'])
			{
				$set[] = 'purchase_quantity=' . (int)$purchase_quantity['purchase_quantity'];
				$fix_purchase_quantity = (int)($purchase_quantity['purchase_quantity'] - $p['purchase_quantity']);
			}
			if ((int)$purchase_actual['purchase_actual_quantity'] != $p['purchase_actual_quantity'])
			{
				$set[] = 'purchase_actual_quantity=' . (int)$purchase_actual['purchase_actual_quantity'];
				$fix_purchase_actual_quantity = (int)($purchase_actual['purchase_actual_quantity'] - $p['purchase_actual_quantity']);
			}
			if ((int)$purchase_budget['purchase_budget_quantity'] != $p['purchase_budget_quantity'])
			{
				$set[] = 'purchase_budget_quantity=' . (int)$purchase_budget['purchase_budget_quantity'];
				$fix_purchase_budget_quantity = (int)($purchase_budget['purchase_budget_quantity'] - $p['purchase_budget_quantity']);
			}
			if ((int)$sold['sold_quantity'] != $p['sold_quantity'])
			{
				$set[] = 'sold_quantity=' . (int)$sold['sold_quantity'];
				$fix_sold_quantity = (int)($sold['sold_quantity'] - $p['sold_quantity']);
			}
			if ($set)
			{
				$sql = 'update product_warehouse set ' . implode(',', $set) . ' where id=' . $p['id'];
				QDB::getConn()->execute($sql);
				QDB::getConn()->execute('insert into fix_log(product_basic_id, fix_available_quantity, fix_purchase_quantity, fix_purchase_actual_quantity, fix_purchase_budget_quantity, fix_sold_quantity, time) values('. $p['product_basic_id'] .', '. $fix_available_quantity .', '. $fix_purchase_quantity .', '. $fix_purchase_actual_quantity .', '. $fix_purchase_budget_quantity .', '. $fix_sold_quantity .', \''. CURRENT_DATETIME .'\')');
			}
			if (abs($fix_available_quantity)>=20 || abs($fix_purchase_quantity)>=20 || abs($fix_purchase_actual_quantity)>=20 || abs($fix_purchase_budget_quantity)>=20 || abs($fix_sold_quantity)>=20)
			{
				$message .= '产品ID：' . $p['product_basic_id'] . '；' . '修复可用' . $fix_available_quantity . '；' . '采购' . $fix_purchase_quantity . '；' . '采购实际' . $fix_purchase_actual_quantity . '；' . '采购预计' . $fix_purchase_budget_quantity . '；' . '采购待发' . $fix_sold_quantity . "\n";
			}
		}
		if ($message)
		{
			require_once (Q::ini('custom_system/lib_dir') . 'PHPMailer/class.phpmailer.php');
			$mail = new PHPMailer();
			$mail->IsSMTP();
			$mail->CharSet = 'UTF-8';
			$mail->Host = 'smtp.qq.com';
			$mail->SMTPAuth = true;
			$mail->Username = '296474670@qq.com';
			$mail->Password = 'QQ3711570';
			$mail->Port = 25;
			$mail->From = '296474670@qq.com';
			$mail->AddAddress('407909571@qq.com', 'zen');
			$mail->AddAddress('455529048@qq.com', 'tony');
			$mail->Subject = '系统修复产品数量日志';
			$mail->Body = $message;
			$mail->Send();
		}
		return array('ack' => SUCCESS, 'message' => 'success fixProductQuantity, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function detectInventoryLog()
	{
		$time = date("Y-m-d", strtotime("-1 day"));
		$message = '';
		
		//重复出入库记录
		foreach (Q::ini('custom_flag/inventory_log_type') as $it)
		{
			$repeat_log = QDB::getConn()->execute('select relate_table_id from inventory_log where relate_table_id != 0 and type='.$it['value'].' and time>=\'' . $time . '\' group by relate_table_id having count(relate_table_id)>1')->fetchAll();
			foreach ($repeat_log as $rl)
			{
				$message .= '【重复出入库记录】 关联表ID ' . $rl['relate_table_id'] . ' （' . $it['name'] . 'Type=' . $it['value'] . '）' . "\n";
			}
		}
		
		//总金额错误&&剩余数量与实际库存不等
		$default_warehouse_id = Warehouse::getDefaultWarehouseId();
		$products = Product_Basic::find()->asArray()->getAll();
		foreach ($products as $p)
		{
			$t_init = $t_remain = $t_out = $t_in = $remaining_quantity = 0;
			$logs = Inventory_Log::find('product_basic_id=? and warehouse_id=?', $p['id'], $default_warehouse_id)->asArray()->getAll();
			foreach ($logs as $l)
			{
				$t_init += $l['init_quantity'] * $l['cost_price'];
				$t_in += $l['in_quantity'] * $l['cost_price'];
				$t_out += $l['out_quantity'] * $l['cost_price'];
				$t_remain += $l['remaining_quantity'] * $l['cost_price'];
				$remaining_quantity += $l['remaining_quantity'];
			}
			$product_in_warehouse = Product_Warehouse::find('product_basic_id=? and warehouse_id=?', $p['id'], $default_warehouse_id)->asArray()->getOne();
			if ($product_in_warehouse && $product_in_warehouse['actual_quantity'] <> $remaining_quantity)
			{
				$message .= '【实际库存与日志不符】 产品ID ' . $p['id'] . ', 库存  ' . $product_in_warehouse['actual_quantity'] . ', 日志记录剩下数量 ' . $remaining_quantity . "\n"; 
			}
			if (abs($t_init+$t_in-$t_out-$t_remain) > 0.01)
			{
				$message .= '【总金额错误】 产品ID ' . $p['id'] . ', 期初总值  ' . $t_init . ', 入库总值 ' . $t_in . ', 出库总值 ' . $t_out . ', 剩余总值 ' . $t_remain . ', 实际差值 ' . ($t_in+$t_init-$t_out-$t_remain) . "\n";
			}
		}
		
		//日志单价*数量不等于总价
		$in_out = QDB::getConn()->execute('select * from inventory_log where (in_quantity <> 0 and abs(cost_price*in_quantity-cost_amount)>0.01) or (out_quantity<>0 and abs(cost_price*out_quantity-cost_amount)>0.01)')->fetchAll();
		if ($in_out)
		{
			foreach ($in_out as $io)
			{
				$message .= '【总价与单价数量积错误】 日志ID ' . $io['id'] . ', 产品ID ' . $io['product_basic_id'] . "\n";
			}
		}
		
		if ($message)
		{
			require_once (Q::ini('custom_system/lib_dir') . 'PHPMailer/class.phpmailer.php');
			$mail = new PHPMailer();
			$mail->IsSMTP();
			$mail->CharSet = 'UTF-8';
			$mail->Host = 'smtp.qq.com';
			$mail->SMTPAuth = true;
			$mail->Username = '296474670@qq.com';
			$mail->Password = 'QQ3711570';
			$mail->Port = 25;
			$mail->From = '296474670@qq.com';
			$mail->AddAddress('407909571@qq.com', 'zen');
			$mail->AddAddress('455529048@qq.com', 'tony');
			$mail->Subject = '检测Log错误结果';
			$mail->Body = $message;
			$mail->Send();
		}
		return array('ack' => SUCCESS, 'message' => 'success detectInventoryLog, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function updateClearanceSummaryLog()
	{
		$default_warehouse_id = Warehouse::getDefaultWarehouseId();
		$clearance = QDB::getConn()->execute('select count(product_warehouse.id) as number_quantity, sum(product_warehouse.actual_quantity) as total_actual_quantity, sum(product_basic.purchase_price) as total_value, product_warehouse.sales_status from product_warehouse left join product_basic on product_warehouse.product_basic_id=product_basic.id where product_warehouse.actual_quantity>0 and product_warehouse.sales_status='.Q::ini('custom_flag/product_sales_status/clearance/value').' and product_warehouse.warehouse_id='.$default_warehouse_id)->fetchRow();
		$sold_out = QDB::getConn()->execute('select count(product_warehouse.id) as number_quantity, sum(product_warehouse.actual_quantity) as total_actual_quantity, sum(product_basic.purchase_price) as total_value, product_warehouse.sales_status from product_warehouse left join product_basic on product_warehouse.product_basic_id=product_basic.id where product_warehouse.actual_quantity>0 and product_warehouse.sales_status='.Q::ini('custom_flag/product_sales_status/sold_out/value').' and product_warehouse.warehouse_id='.$default_warehouse_id)->fetchRow();
		QDB::getConn()->execute('insert into clearance_summary_log(number_quantity,total_actual_quantity,total_value,status,update_time) values('.$clearance['number_quantity'].',' . $clearance['total_actual_quantity'] . ',' . $clearance['total_value'] . ',' . $clearance['sales_status'] . ',\'' . CURRENT_DATETIME . '\')');
		QDB::getConn()->execute('insert into clearance_summary_log(number_quantity,total_actual_quantity,total_value,status,update_time) values('.$sold_out['number_quantity'].',' . $sold_out['total_actual_quantity'] . ',' . $sold_out['total_value'] . ',' . $sold_out['sales_status'] . ',\'' . CURRENT_DATETIME . '\')');
		return array('ack' => SUCCESS, 'message' => 'success updateClearanceSummaryLog, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function updateClearanceRecordLog()
	{
		$default_warehouse_id = Warehouse::getDefaultWarehouseId();
		$clearance = Product_Warehouse::find('product_warehouse.sales_status=? and product_warehouse.warehouse_id=?', Q::ini('custom_flag/product_sales_status/clearance/value'), $default_warehouse_id)
			->joinLeft('product_basic', '', 'product_basic.id=product_warehouse.product_basic_id')
			->setColumns('product_basic_id, available_quantity as quantity, product_basic.last_purchase_price as price')
			->asArray()->getAll();
		foreach ($clearance as $c)
		{
			QDB::getConn()->execute('insert into clearance_record_log(product_basic_id, quantity, amount, update_time) values(' . $c['product_basic_id'] . ', ' . $c['quantity'] . ', ' . $c['price']*$c['quantity'] . ', \'' . CURRENT_DATETIME . '\')');
		}
		return array('ack' => SUCCESS, 'message' => 'success updateClearanceRecordLog, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function getGAData()
	{
		$begin_date = date('Y-m-d', strtotime(CURRENT_DATETIME . ' -2 day'));
		$end_date = date('Y-m-d', strtotime(CURRENT_DATETIME));
		$shop = Shop::find('shop_api_website.ga_profile!=\'\'')->joinLeft('shop_api_website', 'ga_profile, md5', 'shop_api_website.shop_id=shop.id')->asArray()->getAll();
		GA_Data::getGAData($begin_date, $end_date, $shop);
		return array('ack' => SUCCESS, 'message' => 'success getGAData, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function updateProductShelfPosition()
	{
		$product = Product_Shelf_Position::find()->asArray()->getALL();
		foreach ($product as $p)
		{
			$shelf_position = Product_Warehouse::find('shelf_position like \'%' . $p['position'] . '%\'')->setColumns('storage_number as product_number, available_quantity as product_quantity')->asArray()->getAll();
			$product_number_quantity = count($shelf_position);
			$product_quantity = 0;
			foreach ($shelf_position as $sp)
			{
				if (isset($sp['product_quantity']))
				{
					$product_quantity += $sp['product_quantity'];
				}
			}
			QDB::getConn()->execute('update product_shelf_position set product_number_quantity=' . $product_number_quantity . ',product_quantity=' . $product_quantity . ' where id='. $p['id']);
		}
		return array('ack' => SUCCESS, 'message' => 'success updateProductShelfPosition, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function getOnlineEbaySellerList()
	{
		$start_time = date("c", strtotime(date("Y-m-d 00:00:00", strtotime("-3 day"))));
		$time = date("c", strtotime(date("Y-m-d 23:59:59", strtotime("+90 day"))));
		$ebay_shop = QDB::getConn()->execute('select id from shop where platform=\'ebay\' and status=' . Q::ini('custom_flag/shop_status/enabled/value'))->fetchCol();
		$result = Online_Ebay_Listing::getEbaySellerListing($ebay_shop, $start_time, $time, 'down');
		if ($result['ack'] == SUCCESS)
		{
			return array('ack' => SUCCESS, 'message' => 'success getOnlineEbaySellerList, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
		else
		{
			return array('ack' => FAILURE, 'message' => $result['message'] . ', execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
	}
	
	static function onlineEbayListingKeepQuantity()
	{
		$listing = Online_Ebay_listing::find('status=? and keep_flag=?', Q::ini('custom_flag/online_ebay_listing_status/active/value'), Q::ini('custom_flag/online_ebay_listing_keep_flag/opened/value'))->asArray()->getAll();
		foreach ($listing as $l)
		{
			Online_Ebay_Listing::ReviseInventoryStatus($l, sprintf('%.2f', $l['start_price']), $l['keep_quantity']);
		}
		return array('ack' => SUCCESS, 'message' => 'success onlineEbayListingKeepQuantity, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}

	static function getSiteRegCustomerList()
	{
		$bdate = empty($_GET["bdate"]) ? date("Y-m-d", time()-3600*24*3) : $_GET["bdate"];
		$edate = empty($_GET["edate"]) ? date("Y-m-d") : $_GET["edate"];		
		$shopID = empty($_GET["shop_id"]) ? 53 : $_GET["shop_id"];
		$customerMailList = Helper_BSS_API::getZencartCustomerByAPI($shopID, $bdate, $edate);
		
		$insertCount = 0;
		$ignoreCount = 0;
		
		foreach ($customerMailList as $k => $v)
		{
			$customerMailList[$k]["country_name"] = '';
			$isExisted = QDB::getConn()->execute("select email from customer_reg_info where email = '{$v["customers_email_address"]}' and shop_id = '{$shopID}' limit 1 ")->fetchOne();
			if (empty($isExisted))
			{
				QDB::getConn()->insert("customer_reg_info",array(
					"email" => $v["customers_email_address"],
					"reg_time" => $v["customers_info_date_account_created"],
					"last_login_time" => $v["customers_info_date_of_last_logon"],
					"first_name" => $v["customers_firstname"],
					"last_name" => $v["customers_lastname"],
					"shop_id" => $shopID,
				));
				$insertCount++;
			} else {
				$ignoreCount++;
			}
		}
		return array('ack' => SUCCESS, 'message' => "success getSiteRegCustomerList, Inesrt({$insertCount}) Ignore({$ignoreCount}) execute time:" . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');		
	}
	
	static function updateEbayCusmtomerList()
	{
		// 客户邮件服务商 映射关系
		$mailServiceKeywordsMapping = array(
			"@yahoo" => "YahooMail",
			"@ymail" => "YahooMail",
			"@rocketmail" => "YahooMail",
			"@hotmail" => "MsMail",
			"@msn" => "MsMail",
			"@outlook" => "MsMail",
			"@live" => "MsMail",
			"@gmail" => "GoogleMail",
			"@aol" => "AolMail",
		);
		
		$bdate = empty($_GET["bdate"]) ? date("Y-m-d", time()-3600*24*3 ) : $_GET["bdate"];
		$edate = empty($_GET["edate"]) ? date("Y-m-d" ) : $_GET["edate"];	
		
		$allEbayStoreID = Shop::find('platform=?', 'ebay')->asArray()->getAll();
		$allEbayStoreIDArray = array();
		foreach ($allEbayStoreID as $v)
		{
			$allEbayStoreIDArray[] = $v["id"];
		}
		$allEbayStoreIDString = implode(", " , $allEbayStoreIDArray);
		$sql = "SELECT soci.email customers_email_address, so.id oid, soci.id cid, soci.fullname customers_firstname, so.shop_id shop_id, so.id order_id, so.sales_time sales_time, so.shipped_time shipped_time, soci.country_cn country_name
		FROM `sales_order` so, `sales_order_customer_info` soci
		WHERE so.id = soci.sales_order_id
		AND so.shipped_time > '{$bdate} 00:00:00'
		AND so.shipped_time < '{$edate} 23:59:59'
		AND soci.country_cn in (  '美国',  '英国',  '澳大利亚',  '加拿大',  '爱尔兰',  '新西兰' ) 
		AND so.shop_id in (  {$allEbayStoreIDString} ) 
		ORDER BY so.shipped_time DESC";
	
		$customerMailList = QDB::getConn()->execute($sql)->fetchAll();

		$insertCount = 0;
		$ignoreCount = 0;

		foreach ($customerMailList as $k => $v) {
			
			$v["mail_service"] = "Other";
			
			foreach ($mailServiceKeywordsMapping as $mskm_key => $mskm_value) {
				
				if (strpos($v["customers_email_address"], $mskm_key)!==false) {
					$v["mail_service"] = $mskm_value; 
					break;
				}
			}

			$sql = "SELECT soi.product_basic_id, soi.sales_order_id, pb.product_categories_id
				FROM `sales_order_item` soi, product_basic pb
				WHERE soi.product_basic_id = pb.id and soi.sales_order_id = {$v["oid"]} 
				group by pb.product_categories_id";
			
			$ordernProductCatInfo = QDB::getConn()->execute($sql)->fetchAll();
			
			foreach($ordernProductCatInfo as $opcv) {
				
				$parentArray = Product_Categories::getAncestorById($opcv["product_categories_id"]);
				
				$customerMailList[$k]["customers_lastname"] = '';
				$customerMailList[$k]["customers_info_date_of_last_logon"] = '';
				$isExisted = QDB::getConn()->execute("select email from sales_order_customer_categories where email = '".addslashes($v["customers_email_address"])."' and category_id = '{$parentArray[0]["id"]}'  limit 1 ")->fetchOne();
				
				if (empty($isExisted)) {
					
					QDB::getConn()->insert("sales_order_customer_categories",array(
						"customer_id" => $v["cid"],
						"email" => $v["customers_email_address"],
						"email_server" => $v["mail_service"],
						"fullname" => $v["customers_firstname"],
						"order_id" => $v["order_id"],
						"category_id" => $parentArray[0]["id"],
						"order_time" => $v["sales_time"],
						"shipped_time" => $v["shipped_time"],
						"shop_id" => $v["shop_id"],
					));
					$insertCount++;
					//echo "Insert: {$v["sales_time"]} {$v["oid"]} > {$opcv["product_basic_id"]} {$parentArray[0]["id"]} | {$v["customers_email_address"]} By {$v["mail_service"]} <br />\n";
				} else {
					$ignoreCount++;
					//echo "Existed: {$v["sales_time"]} {$v["oid"]} > {$opcv["product_basic_id"]} {$parentArray[0]["id"]} | {$v["customers_email_address"]} By {$v["mail_service"]} <br />\n";
				}
				
			}
			
		}
		
		return array('ack' => SUCCESS, 'message' => "success updateEbayCusmtomerList, Inesrt({$insertCount}) Ignore({$ignoreCount}) execute time:" . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}

	/**
	 * 更新客户订单统计表
	 */
	static function updateCustomerOrdersInfo() {
		
		$bdate = empty($_GET["bdate"]) ? date("Y-m-d", time()-3600*24*3000 ) : $_GET["bdate"];
		$edate = empty($_GET["edate"]) ? date("Y-m-d" ) : $_GET["edate"];	

		// 取订单信息
		$olsql = "SELECT id FROM `sales_order` "; 
		$orderList = QDB::getConn()->execute($olsql)->fetchAll();
		$totalTaskCount = count($orderList);
		
		echo "<h1> Total Cutomers: ".$totalTaskCount."</h1>";
		echo "<hr />";
		
		$startTime = time();
		
		foreach ($orderList as $ok => $ov) {
			
			// 取客户 id email 信息
			$cisql = "SELECT id, email FROM `sales_order_customer_info` where sales_order_id = '{$ov["id"]}' "; 

			$customerInfo = QDB::getConn()->execute($cisql)->fetchRow();
			
			if (empty($customerInfo["email"])) continue;
			
			$isExisted = QDB::getConn()->execute("select email from customer_orders_info where email = '".addslashes($customerInfo["email"])."'  limit 1 ")->fetchOne();
			
			if (!empty($isExisted)) {
				echo "<div>existed ".($ok+1)."/{$totalTaskCount} | {$customerInfo["id"]} / {$customerInfo["email"]}</div>"; 	
				continue;
			}

			// 初次订单信息
			$firstOrderSql = "select so.id as first_order_id, so.sales_time as first_order_time, so.shop_id as first_order_shop_id, soci.fullname as first_order_full_name, soci.country_cn as first_order_country_cn ";
			$firstOrderSql .= " from sales_order so, sales_order_customer_info soci ";
			$firstOrderSql .= " where so.id = soci.sales_order_id and soci.email = '".addslashes($customerInfo["email"])."' ";
			$firstOrderSql .= " order by so.sales_time asc limit 1 ";
			
			$firstOrderInfo = QDB::getConn()->execute($firstOrderSql)->fetchRow();
			
			// 最新订单信息
			$lastOrderSql = "select so.id as last_order_id, so.sales_time as last_order_time ";
			$lastOrderSql .= "from sales_order so, sales_order_customer_info soci ";
			$lastOrderSql .= "where so.id = soci.sales_order_id and soci.email = '".addslashes($customerInfo["email"])."' ";
			$lastOrderSql .= "order by so.sales_time desc limit 1";
			$lastOrderInfo = QDB::getConn()->execute($lastOrderSql)->fetchRow();
				
			// 订单总数
			$orderCountSql = "select count(so.id) as order_total_count ";
			$orderCountSql .= "from sales_order so, sales_order_customer_info soci ";
			$orderCountSql .= "where so.id = soci.sales_order_id and soci.email = '".addslashes($customerInfo["email"])."' ";
			$orderCount = QDB::getConn()->execute($orderCountSql)->fetchOne();
			$orderCount = intval($orderCount);
			
			// 订单周期
			$orderCycle = $orderCount == 1  ? 0 : ( ( strtotime($lastOrderInfo["last_order_time"])-strtotime($firstOrderInfo["first_order_time"]) ) / 86400 ) / ( $orderCount - 1 );
			
			$orderCycle = round($orderCycle, 2)."D";

			// 订单总金额
			$orderAmountSql = "select so.amount as amount, so.currency_id, so.currency_rate as currency_rate ";
			$orderAmountSql .= "from sales_order so, sales_order_customer_info soci ";
			$orderAmountSql .= "where so.id = soci.sales_order_id and soci.email = '".addslashes($customerInfo["email"])."' ";
			$orderAmountInfo = QDB::getConn()->execute($orderAmountSql)->fetchAll();
			
			$orderAmount = 0;
			foreach ($orderAmountInfo as $key => $value) {
				$orderAmount += round($value['amount'],4)*round($value["currency_rate"],4);
			}
			$orderAmount = round($orderAmount, 2);

			// 订单产品类别信息
			$orderItemsSql = "select soi.sales_order_id as oid, soi.quantity as pquantity, soi.sales_price as pprice, soi.product_basic_id as pid ";
			$orderItemsSql .= "from sales_order_item soi, sales_order_customer_info soci ";
			$orderItemsSql .= "where soi.sales_order_id = soci.sales_order_id and soci.email = '".addslashes($customerInfo["email"])."' ";
			$orderItemsInfo = QDB::getConn()->execute($orderItemsSql)->fetchAll();

			$orderCategoriesOrders = array();
			$orderCategoriesAmount = array();
			$orderCurrencyRate = array();
			
			foreach ($orderItemsInfo as $key => $value) {
						
				$itemCategoryID = QDB::getConn()->execute("select product_categories_id from product_basic where id = {$value["pid"]}")->fetchOne();		
				
				if (isset($orderCategoriesOrders[$itemCategoryID])) {
						$orderCategoriesOrders[$itemCategoryID][$value["oid"]] = 1;
				} else {
					$orderCategoriesOrders[$itemCategoryID] = array($value["oid"]=>1);
				}
						
				if (!isset($orderCurrencyRate[$value["oid"]])) {
					$orderCurrencyRate[$value["oid"]] = QDB::getConn()->execute("select currency_rate from sales_order where id = {$value["oid"]}")->fetchOne();
					$orderCurrencyRate[$value["oid"]] = round($orderCurrencyRate[$value["oid"]],4);
				}
				
				
				if (isset($orderCategoriesAmount[$itemCategoryID])) {
					$orderCategoriesAmount[$itemCategoryID] += $orderCurrencyRate[$value["oid"]]*intval($value["pquantity"])*round($value["pprice"],4);
				} else {
					$orderCategoriesAmount[$itemCategoryID] = $orderCurrencyRate[$value["oid"]]*intval($value["pquantity"])*round($value["pprice"],4);
				}
			}
			
			foreach ($orderCategoriesOrders as $k => $v) {
				$orderCategoriesOrders[$k] = count($v);
			}
			
			arsort($orderCategoriesOrders);
			arsort($orderCategoriesAmount);
			
			$topCountCategoryID = array_shift(array_keys($orderCategoriesOrders));
			$topAmountCategoryID = array_shift(array_keys($orderCategoriesAmount));
			
			$topCountCategories = Product_Categories::getAncestorById($topCountCategoryID);
			$topAmountCategories = Product_Categories::getAncestorById($topAmountCategoryID);
			
			$topCountCategoriesArray = array();
			
			foreach ($topCountCategories as $v) {
				$topCountCategoriesArray[] = $v["name"];
			}
			
			$topAmountCategoriesArray = array();
			
			foreach ($topAmountCategories as $v) {
				$topAmountCategoriesArray[] = $v["name"];
			}
				
			QDB::getConn()->insert("customer_orders_info",array(
					"email" => $customerInfo["email"],
					"first_order_full_name" => $firstOrderInfo["first_order_full_name"],
					"first_order_country_cn" => $firstOrderInfo["first_order_country_cn"],
					"first_order_id" => $firstOrderInfo["first_order_id"],
					"first_order_shop_id" => $firstOrderInfo["first_order_shop_id"],
					"first_order_time" => $firstOrderInfo["first_order_time"],
					"last_order_id" => $lastOrderInfo["last_order_id"],
					"last_order_time" => $lastOrderInfo["last_order_time"],
					"order_total_count" => $orderCount,
					"order_total_amount_cny" => $orderAmount,
					"order_count_top_categroy_id" => $topCountCategoryID,
					"order_amount_top_categroy_id" => $topAmountCategoryID,
					"order_cycle" => $orderCycle,
					"update_time" => date("Y-m-d H:i:s"),
			));			
			
			echo "<div>insert ".($ok+1)."/{$totalTaskCount} | {$customerInfo["id"]} / {$customerInfo["email"]}</div>"; 
			
			ob_flush();
			
		}	
		
	}
	
	static function getAliExpressLoanInfo()
	{
		$shop = Shop::find('platform=?', 'aliexpress')->asArray()->getAll();
		foreach($shop as $s)
		{
			$access_token = Shop_API_AliExpress::getAccessTokenByShopId($s['id']);
			if ($access_token['ack'] == SUCCESS)
			{
				$after_90_days = date('Y-m-d H:i:s', CURRENT_TIMESTAMP - 3600 * 24 * 90);
				$order = Sales_Order::find('shop_id=? and shop_order_number!=? and sales_time>=? and loan_amount is null and loan_time is null', $s['id'], '0', $after_90_days)
					->setcolumns('id,shop_order_number')->asArray()->getAll();
				if ($order)
				{
					$order_info_uri = Q::ini('custom_api/aliexpress_uri') . 'api.findOrderById/' . Q::ini('custom_api/aliexpress_app_key');
					foreach ($order as $o)
					{
						$param = array(
							'access_token' => $access_token['access_token'],
							'orderId' => $o['shop_order_number']
						);
						$result = Helper_BSS_API::AliExpressAPICall($order_info_uri, $param, false);
						if (isset($result['loanInfo']['loanAmount']['amount']) && isset($result['loanInfo']['loanAmount']['currencyCode']) && isset($result['loanInfo']['loanTime']))
						{
							QDB::getConn()->execute("update sales_order set loan_amount=" . $result['loanInfo']['loanAmount']['amount'] . ",loan_currency_code='" . $result['loanInfo']['loanAmount']['currencyCode'] . "',loan_time='" . Helper_BSS_Normal::AliExpressDateTransfer($result['loanInfo']['loanTime']) . "' where id=" . $o['id']);
						}
					}
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => 'success getAliexpressLoanInfo, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function getAmazon2ReportRequestId()
	{
		$amazon_shop_ids = QDB::getConn()->execute('select id from shop where status=' . Q::ini('custom_flag/shop_status/enabled/value') . ' and platform=\'amazon2\'')->fetchCol();
		$result = Helper_BSS_API::getAmazonReportRequestId($amazon_shop_ids);
		return array('ack' => $result['ack'], 'message' => $result['message']);
	}
	
	static function getAmazon2ReportListId()
	{
		$amazon_shops = Shop_API_amazon::find('shop.platform=\'amazon2\' and online_amazon_task.request_id is not NULL and online_amazon_task.report_id is NULL and online_amazon_task.request_time>=\'' . date('Y-m-d', CURRENT_TIMESTAMP) . '\'')
			->joinLeft('online_amazon_task', 'id as online_amazon_task_id, request_id', 'online_amazon_task.shop_id=shop_api_amazon.shop_id')
			->joinLeft('shop', 'name', 'shop.id=shop_api_amazon.shop_id')
			->order('shop_id desc')
			->asArray()->getAll();
		$result = Helper_BSS_API::getAmazonReportListId($amazon_shops);
		return array('ack' => $result['ack'], 'message' => $result['message']);
	}
	
	static function getAmazonReportRequestId()
	{
		$amazon_shop_ids = QDB::getConn()->execute('select id from shop where status=' . Q::ini('custom_flag/shop_status/enabled/value') . ' and platform=\'amazon\'')->fetchCol();
		$result = Helper_BSS_API::getAmazonReportRequestId($amazon_shop_ids);
		return array('ack' => $result['ack'], 'message' => $result['message']);
	}
	
	static function getAmazonReportListId()
	{
		$amazon_shops = Shop_API_amazon::find('shop.platform=\'amazon\' and online_amazon_task.request_id is not NULL and online_amazon_task.report_id is NULL and online_amazon_task.request_time>=\'' . date('Y-m-d', CURRENT_TIMESTAMP) . '\'')
			->joinLeft('online_amazon_task', 'id as online_amazon_task_id, request_id', 'online_amazon_task.shop_id=shop_api_amazon.shop_id')
			->joinLeft('shop', 'name', 'shop.id=shop_api_amazon.shop_id')
			->order('shop_id desc')
			->asArray()->getAll();
		$result = Helper_BSS_API::getAmazonReportListId($amazon_shops);
		return array('ack' => $result['ack'], 'message' => $result['message']);
	}
	
	static function updateOnlineAmazonMyPrice()
	{
		$amazon_shops = Shop_API_Amazon::find()
			->joinLeft('shop', 'name', 'shop.id=shop_api_amazon.shop_id')
			->asArray()->getAll();
		$message = '';
		foreach ($amazon_shops as $amazon_shop)
		{
			$service_url = 'https://' . $amazon_shop['mws_endpoint'] . '/Products/' . Q::ini('custom_api/amazon_mws_services_order_version');
			$config = array (
				'ServiceURL' => $service_url,
				'ProxyHost' => $amazon_shop['proxy_host'] ? $amazon_shop['proxy_host'] : null,
				'ProxyPort' => $amazon_shop['proxy_port'] ? $amazon_shop['proxy_port'] : -1,
				'MaxErrorRetry' => 3,
			);
			$service = new MarketplaceWebServiceProducts_Client(
				$amazon_shop['access_key'],
				$amazon_shop['secret_key'],
				$amazon_shop['application_name'],
				$amazon_shop['version'],
				$config
			);
			$online_amazon_products = array_chunk(QDB::getConn()->execute('select * from online_amazon_product where shop_id=' . $amazon_shop['shop_id'] . ' and status=' . Q::ini('custom_flag/online_amazon_product_status/onsale/value') . ' and (shipping_fee is NULL or to_days(update_time)<>to_days(now()))')->fetchAll(), 20);
			foreach ($online_amazon_products as $online_amazon_product)
			{
				$sku_list = array();
				foreach ($online_amazon_product as $oap)
				{
					$sku_list[] = $oap['sku']; 
				}
				$request = new MarketplaceWebServiceProducts_Model_GetMyPriceForSKURequest();
				$request->setMarketplaceId($amazon_shop['marketplace_id']);
				$request->setSellerId($amazon_shop['merchant_id']);
				$request->setSellerSKUList($sku_list);
				$response = $service->getMyPriceForSKU($request);
				$get_result_list = $response->getGetMyPriceForSKUResult();
				foreach ($get_result_list as $get_result)
				{
					if ($get_result->isSetSellerSKU())
					{
						$asin = $get_result->getSellerSKU();
						if ($get_result->isSetProduct())
						{
							$product = $get_result->getProduct();
							if ($product->isSetOffers())
							{
								$offerList = $product->getOffers()->getOffer();
								if (empty($offerList[0]))
								{
									QDB::getConn()->execute('update online_amazon_product set update_time=\'' . CURRENT_DATETIME . '\' where sku=\'' . $sku . '\' and shop_id=' . $amazon_shop['shop_id']);
									continue;
								}
								else
								{
									$price = $offerList[0]->getBuyingPrice();
									QDB::getConn()->execute('update online_amazon_product set price=' . $price->getListingPrice()->getAmount() . ', shipping_fee=' . $price->getShipping()->getAmount() . ', update_time=\'' . CURRENT_DATETIME . '\' where sku=\'' . $sku . '\' and shop_id=' . $amazon_shop['shop_id']);
								}
							}
						}
					}
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => 'success updateOnlineAmazonMyPrice, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function updateOnlineAmazonLowestPrice()
	{
		$amazon_shops = Shop_API_Amazon::find()
			->joinLeft('shop', 'name', 'shop.id=shop_api_amazon.shop_id')
			->asArray()->getAll();
		$message = '';
		foreach ($amazon_shops as $amazon_shop)
		{
			$service_url = 'https://' . $amazon_shop['mws_endpoint'] . '/Products/' . Q::ini('custom_api/amazon_mws_services_order_version');
			$config = array (
				'ServiceURL' => $service_url,
				'ProxyHost' => $amazon_shop['proxy_host'] ? $amazon_shop['proxy_host'] : null,
				'ProxyPort' => $amazon_shop['proxy_port'] ? $amazon_shop['proxy_port'] : -1,
				'MaxErrorRetry' => 3,
			);
			$service = new MarketplaceWebServiceProducts_Client(
				$amazon_shop['access_key'],
				$amazon_shop['secret_key'],
				$amazon_shop['application_name'],
				$amazon_shop['version'],
				$config
			);
			$online_amazon_products = array_chunk(QDB::getConn()->execute('select * from online_amazon_product where shop_id=' . $amazon_shop['shop_id'] . ' and status=' . Q::ini('custom_flag/online_amazon_product_status/onsale/value') . ' and (to_days(update_lowest_time)<>to_days(now()) or update_lowest_time is NULL)')->fetchAll(), 20);
			foreach ($online_amazon_products as $online_amazon_product)
			{
				$sku_list = array();
				foreach ($online_amazon_product as $oap)
				{
					$sku_list[] = $oap['sku']; 
				}
				$request = new MarketplaceWebServiceProducts_Model_GetLowestOfferListingsForSKURequest();
				$request->setMarketplaceId($amazon_shop['marketplace_id']);
				$request->setSellerId($amazon_shop['merchant_id']);
				$request->setASINList($sku_list);
				try
				{
					$response = $service->getLowestOfferListingsForSKU($request);
					$get_result_list = $response->getGetLowestOfferListingsForSKUResult();
					foreach ($get_result_list as $get_result)
					{
						if ($get_result->isSetSellerSKU())
						{
							$sku = $get_result->getSellerSKU();
							if ($get_result->isSetProduct())
							{
								$product = $get_result->getProduct();
								$lowest_price_list = $product->getLowestOfferListings()->getLowestOfferListing();
								if (isset($lowest_price_list[0]) && $lowest_price_list[0])
								{
									$price = $lowest_price_list[0]->getPrice();
									QDB::getConn()->execute('update online_amazon_product set lowest_price=' . $price->getListingPrice()->getAmount() . ', lowest_price_shipping=' . $price->getShipping()->getAmount() . ', update_lowest_time=\'' . CURRENT_DATETIME . '\' where sku=\'' . $sku . '\' and shop_id=' . $amazon_shop['shop_id']);
								}
								else
								{
									QDB::getConn()->execute('update online_amazon_product set update_lowest_time=\'' . CURRENT_DATETIME . '\' where sku=\'' . $sku . '\' and shop_id=' . $amazon_shop['shop_id']);
									continue;
								}
							}
						}
					}
				}
				catch (MarketplaceWebServiceProducts_Exception $ex)
				{
					return array('ack' => PARTIAL_FAILURE, 'message' => 'update ' . $amazon_shop['name'] . ' ' . $ex->getMessage() . '. time:' . CURRENT_DATETIME);
					exit;
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => 'success updateOnlineAmazonLowestPrice, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function updateOnlineAmazonCompetitivePrice()
	{
		$amazon_shops = Shop_API_Amazon::find()
			->joinLeft('shop', 'name', 'shop.id=shop_api_amazon.shop_id')
			->asArray()->getAll();
		$message = '';
		foreach ($amazon_shops as $amazon_shop)
		{
			$service_url = 'https://' . $amazon_shop['mws_endpoint'] . '/Products/' . Q::ini('custom_api/amazon_mws_services_order_version');
			$config = array (
				'ServiceURL' => $service_url,
				'ProxyHost' => $amazon_shop['proxy_host'] ? $amazon_shop['proxy_host'] : null,
				'ProxyPort' => $amazon_shop['proxy_port'] ? $amazon_shop['proxy_port'] : -1,
				'MaxErrorRetry' => 3,
			);
			$service = new MarketplaceWebServiceProducts_Client(
				$amazon_shop['access_key'],
				$amazon_shop['secret_key'],
				$amazon_shop['application_name'],
				$amazon_shop['version'],
				$config
			);
			$online_amazon_products = array_chunk(QDB::getConn()->execute('select * from online_amazon_product where shop_id=' . $amazon_shop['shop_id'] . ' and status=' . Q::ini('custom_flag/online_amazon_product_status/onsale/value') . ' and (to_days(update_competitive_time)<>to_days(now()) or update_competitive_time is NULL)')->fetchAll(), 15);
			foreach ($online_amazon_products as $k => $online_amazon_product)
			{
				$sku_list = array();
				foreach ($online_amazon_product as $oap)
				{
					$sku_list[] = $oap['sku']; 
				}
				$request = new MarketplaceWebServiceProducts_Model_GetCompetitivePricingForSKURequest();
				$request->setMarketplaceId($amazon_shop['marketplace_id']);
				$request->setSellerId($amazon_shop['merchant_id']);
				$request->setSellerSKUList($sku_list);
				try
				{
					$response = $service->getCompetitivePricingForSKU($request);
					$get_result_list = $response->getGetCompetitivePricingForSKUResult();
					foreach ($get_result_list as $get_result)
					{
						if ($get_result->isSetSellerSKU())
						{
							$sku = $get_result->getSellerSKU();
							if ($get_result->isSetProduct())
							{
								$product = $get_result->getProduct();
								$competitive_price_list = $product->getCompetitivePricing()->getCompetitivePrices()->getCompetitivePrice();
								if (isset($competitive_price_list[0]) && $competitive_price_list[0])
								{
									$price = $competitive_price_list[0]->getPrice();
									QDB::getConn()->execute('update online_amazon_product set competitive_price=' . $price->getListingPrice()->getAmount() . ', competitive_price_shipping=' . $price->getShipping()->getAmount() . ', update_competitive_time=\'' . CURRENT_DATETIME . '\' where sku=\'' . $sku . '\' and shop_id=' . $amazon_shop['shop_id']);
								}
								else
								{
									QDB::getConn()->execute('update online_amazon_product set update_competitive_time=\'' . CURRENT_DATETIME . '\' where sku=\'' . $sku . '\' and shop_id=' . $amazon_shop['shop_id']);
									continue;
								}
							}
						}
					}
				}
				catch (MarketplaceWebServiceProducts_Exception $ex)
				{
					return array('ack' => PARTIAL_FAILURE, 'messsage' => 'update ' . $amazon_shop['name'] . ' ' . $ex->getMessage() . '. time:' . CURRENT_DATETIME);
					exit;
				}
			}
		}
		return array('ack' => SUCCESS, 'message' => 'success updateOnlineAmazonCompetitivePrice, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function getEbayMessage()
	{
		$ebay_shop = QDB::getConn()->execute('select id from shop where platform=\'ebay\' and status=' . Q::ini('custom_flag/shop_status/enabled/value'))->fetchCol();
		$start_time = date('c', strtotime("-3 day"));
		$result = Services_Ebay_Message::getListByGetMyMessage($ebay_shop, $start_time, date('c', CURRENT_TIMESTAMP), '0');
		if ($result['ack'] == SUCCESS)
		{
			return array('ack' => SUCCESS, 'message' => 'success getEbayMessage, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
		else
		{
			return array('ack' => FAILURE, 'message' => $result['message'] . ', execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
	}
	
	static function getEbayCase()
	{
		$ebay_shop = QDB::getConn()->execute('select id from shop where platform=\'ebay\' and status=' . Q::ini('custom_flag/shop_status/enabled/value'))->fetchCol();
		$start_time = date('c', strtotime("-45 day"));
		$result = Services_Ebay_Case::getUserCases($ebay_shop, $start_time, date('c', CURRENT_TIMESTAMP));
		if ($result['ack'] == SUCCESS)
		{
			return array('ack' => SUCCESS, 'message' => 'success getEbayCase, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
		else
		{
			return array('ack' => FAILURE, 'message' => $result['message'] . ', execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
	}
	
	static function updateInventoryReport()
	{
		$time = date('Y-m-d 23:59:59', strtotime('-1 day'));
		$result = Inventory_Report::report($time);
		if ($result['ack'] == SUCCESS)
		{
			return array('ack' => SUCCESS, 'message' => 'success updateInventoryReport, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
		else
		{
			return array('ack' => FAILURE, 'message' => $result['message'] . ', execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
	}
	
	static function updateProductImgUrl()
	{
		set_time_limit(0);
		$content = file_get_contents(Q::ini('custom_api/product_img_index_api'));
		if ($content)
		{
			$index = json_decode($content, true);
			$products = Product_Basic::find()->asArray()->setColumns('id, number')->getAll();
			foreach ($products as $p)
			{
				$img = array();
				if (isset($index[$p['number']]))
				{
					$img = $index[$p['number']];
				}
				QDB::getConn()->execute('update product_basic set img=\'' . json_encode($img) . '\' where id=' . $p['id']);
			}
			return array('ack' => SUCCESS, 'message' => 'success updateProductImgUrl, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
		else
		{
			return array('ack' => FAILURE, 'message' => '获取索引失败, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
	}
	
	static function updateFBASalesOrdersAPIStatus() 
	{
		$amazon_shop_ids = array_chunk(QDB::getConn()->execute('select sales_order.shop_order_number from sales_order left join warehouse on warehouse.id=sales_order.warehouse_id where warehouse.name=\'FBA_US\' and sales_order.api_status is NULL and sales_order.handle_status!=' . Q::ini('custom_flag/sales_order_handle_status/shipped/value'))->fetchCol(), 19);
		$amazon_shop = Shop_API_Amazon::find('shop.name=?', 'yes_amz_us')
			->joinLeft('shop', '', 'shop.id=shop_api_amazon.shop_id')
			->asArray()->getOne();
		$service_url = 'https://' . $amazon_shop['mws_endpoint'] . '/Orders/' . Q::ini('custom_api/amazon_mws_services_order_version');
		$config = array (
				'ServiceURL' => $service_url,
				'ProxyHost' => $amazon_shop['proxy_host'] ? $amazon_shop['proxy_host'] : null,
				'ProxyPort' => $amazon_shop['proxy_port'] ? $amazon_shop['proxy_port'] : -1,
				'MaxErrorRetry' => 3,
		);
		$service = new MarketplaceWebServiceOrders_Client(
			$amazon_shop['access_key'],
			$amazon_shop['secret_key'],
			$amazon_shop['application_name'],
			$amazon_shop['version'],
			$config
		);
		foreach ($amazon_shop_ids as $asi)
		{
			$request = new MarketplaceWebServiceOrders_Model_GetOrderRequest();
			$request->setSellerId($amazon_shop['merchant_id']);
			$request->setAmazonOrderId($asi);
			$response = $service->getOrder($request);
			if ($response->isSetGetOrderResult())
			{
				$getOrderResult = $response->getGetOrderResult();
				if ($getOrderResult->isSetOrders())
				{
					$orders = $getOrderResult->getOrders();
					$orderList = $orders->getOrder();
					foreach ($orderList as $order)
					{
						if ($order->isSetAmazonOrderId()) 
						{
							if ($order->isSetOrderStatus())
							{
								QDB::getConn()->execute('update sales_order set api_status=\'' . $order->getOrderStatus() . '\' where shop_order_number=\'' . $order->getAmazonOrderId() . '\' and shop_id=' . $amazon_shop['shop_id']);
							}
						}
					}
				}
			}
			return array('ack' => SUCCESS, 'message' => 'success updateFBASalesOrdersAPIStatus, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
		}
	}
	
	static function checkCronRepeat()
	{
		$start_time = date('Y-m-d 00:00:00', strtotime('now'));
		$end_time = date('Y-m-d 00:00:00', strtotime('+7 days'));
		$result = Task::checkCronRepeat($start_time, $end_time);
		if ($result['ack'] == SUCCESS)
		{
			if ($result['repeat_task'])
			{
				$message = "任务列表在最近一周中\n";
				foreach ($result['repeat_task'] as $k => $r)
				{
					$message .= $k . '、名称为 ”' . implode('“，”', $r['name']) . "“ 的任务执行时间有冲突，分别是以下时间段：\n    " . implode("\n    ", $r['repeat_time']) . "\n";
				}
				require_once (Q::ini('custom_system/lib_dir') . 'PHPMailer/class.phpmailer.php');
				$mail = new PHPMailer();
				$mail->IsSMTP();
				$mail->CharSet = 'UTF-8';
				$mail->Host = 'smtp.qq.com';
				$mail->SMTPAuth = true;
				$mail->Username = '296474670@qq.com';
				$mail->Password = 'QQ3711570';
				$mail->Port = 25;
				$mail->From = '296474670@qq.com';
				$mail->AddAddress('864566282@qq.com', 'peter');
				$mail->AddAddress('407909571@qq.com', 'zen');
				$mail->AddAddress('455529048@qq.com', 'tony');
				$mail->Subject = '检测Cron任务执行时间冲突';
				$mail->Body = $message;
				$mail->Send();	
			}
		}
		return array('ack' => SUCCESS, 'message' => 'success checkCronRepeat, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function autoImportAmazonOrder()
	{
		set_time_limit(0);
		$start_time = date('Y-m-d H:i:s', strtotime('-1 days'));
		$end_time = date('Y-m-d H:i:s', CURRENT_TIMESTAMP-600);
		$shop = Shop_API_Amazon::find('shop.platform=?', 'amazon')
			->joinLeft('shop', '', 'shop.id=shop_api_amazon.shop_id')
			->asArray()->getAll();
		Helper_BSS_API::AutoImportAmazonOrder($shop, $start_time, $end_time);
		return array('ack' => SUCCESS, 'message' => 'success autoImportAmazonOrder, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function autoImportAmazon2Order()
	{
		set_time_limit(0);
		$start_time = date('Y-m-d H:i:s', strtotime('-1 days'));
		$end_time = date('Y-m-d H:i:s', CURRENT_TIMESTAMP-600);
		$shop = Shop_API_Amazon::find('shop.platform=?', 'amazon2')
			->joinLeft('shop', '', 'shop.id=shop_api_amazon.shop_id')
			->asArray()->getAll();
		Helper_BSS_API::AutoImportAmazonOrder($shop, $start_time, $end_time);
		return array('ack' => SUCCESS, 'message' => 'success autoImportAmazonOrder, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function autoImportEbayOrder()
	{
		$mod_time_from = date('c', strtotime('-12 hours'));
		$mod_time_to = date('c', CURRENT_TIMESTAMP);
		$ebay_shop = Shop::find('platform=? and status=?', 'ebay', Q::ini('custom_flag/shop_status/enabled/value'))->asArray()->getAll();
		foreach ($ebay_shop as $s)
		{
			$order = Sales_Order::getEbayOrderByAPI($s['id'], $mod_time_from, $mod_time_to);
			if ($order['ack'] == SUCCESS)
			{
				$result = Sales_Order::import($order['data']);
			}
			else
			{
				$result = Sales_Order::import($order['data']);
				return array('ack' => FAILURE, 'message' => $order['message'] . ', execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
			}
		}
		return array('ack' => SUCCESS, 'message' => 'success autoImportEbayOrder, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
	
	static function updateCK1SalesOrderTracking()
	{
		set_time_limit(0);
		$sales_order = Sales_Order::find('synchronous_flag=? and lsp_number is not NULL and postage is NULL', Q::ini('custom_flag/sales_order_synchronous_flag/CK1/value'))->asArray()->getAll();
		foreach ($sales_order as $so)
		{
			Sales_Order::CK1GetPostage($so['lsp_number']);
		}
		return array('ack' => SUCCESS, 'message' => 'success updateCK1SalesOrderTracking, execute time:' . sprintf('%.4f', microtime(true) - $GLOBALS['g_boot_time']) . '(secs)');
	}
}